package com.cybersoft.jdbc.gavin.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class BaseDao
{

	/*private final String DB_DRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private final String DB_URL="jdbc:sqlserver://localhost:1433;DatabaseName=QBlog";
	private final String DB_USER="sa";
	private final String DB_PWD="123";*/
	
	/*
	 * mysql 
	private final String DB_DRIVER="com.mysql.jdbc.Driver";
	private final String DB_URL="jdbc:mysql://localhost:3306/news";
	
	private final String DB_USER="root";
	private final String DB_PWD="root";
	
	*/
	
	 
	private static final String DB_URL = "jdbc:oracle:thin:@192.168.93.76:1521:orcl";
	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_USER = "PMS";
	private static final String DB_PWD = "PMS";
	 
 
	
	public Connection conn=null;
	public PreparedStatement pstat=null;
	public ResultSet res=null;
	
	/**
	 * Purpose:獲取Connection
	 * @return	返回Connection
	 * @return Connection
	 */
	public Connection getConn()
	{
		
		Connection conn = null;
		
		try
		{
			
			Class.forName(DB_DRIVER);
			conn=DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
			conn.setAutoCommit(true);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return conn;
		
	}
	
	
	
	/**
	 * Purpose:執行sql更新語句
	 * @param strSql 
	 * @param para
	 * @return 執行影響的行數
	 * @return int
	 */
	public int execUpdate(String strSql,Object [] para)
	{
		
		int count=0;
		
		try
		{
			conn=this.getConn();
			pstat=conn.prepareStatement(strSql);
			
			if(para!=null)
			{
				for(int i=0;i<para.length;i++)
				{
					pstat.setObject((i+1), para[i]);
				}
			}
			
			count=pstat.executeUpdate();
			
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			this.closeAll();
		}
		
		
		return count;
		
	}
	
	
	/**
	* Purpose: 執行sql查詢
	 * @param sql
	 * @param para
	 * @return 查詢結果集
	 * @return ResultSet
	 */
	public ResultSet execQuery(String sql,Object [] para)
	{
		
		try
		{
			conn=this.getConn();
			
			pstat=conn.prepareStatement(sql);
			
			if(para!=null)
			{
				for(int i=0;i<para.length;i++)
				{
					pstat.setObject((i+1), para[i]);
				}
				
			}
			
			res=pstat.executeQuery();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		/*finally
		{
			this.closeAll(conn, pstat, null);
		}*/
		
		return res;
		
		
	}
	
	
	/**
	 * Purpose:關閉鏈接
	 * @return void
	 */
	public void closeAll()
	{
		try
		{
			
			 if(res!=null)
			 {
				 res.close();
			 }
			 if(pstat!=null)
			 {
				 pstat.close();
			 }
			 if(conn!=null)
			 {
				 conn.close();
			 }
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		 
	}
	
}
